package cn.y.operation.framework.utils.dev;

import cn.y.operation.framework.constants.Constants;
import cn.y.operation.framework.enums.ResponseMsgEnum;
import cn.y.operation.framework.pojo.ExportStatus;
import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.util.ResourceUtils;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;

@SuppressWarnings("rawtypes")
public class ExcelUtil {
    public static DateFormat defaultDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 初始化
     *
     * @throws IOException
     */
    public static Workbook getWorkbook(String filePath) throws Exception {
        InputStream inputStream = new FileInputStream(ResourceUtils.getFile(filePath));
        try {
            if (filePath.endsWith(".xlsx")) {
                return new XSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xls")) {
                return new HSSFWorkbook(inputStream);
            } else {
                throw new RuntimeException("模板无效 , " + filePath + " , eg:.xlsx or .xls");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    /**
     * 初始化
     *
     * @param <K>
     * @throws IOException
     */
    public static <T, K> Workbook export(String filePath, K dataMap, List<T> dataList) throws Exception {
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(filePath);
        Resource resource = resources[0];
        //获得文件流，因为在jar文件中，不能直接通过文件资源路径拿到文件，但是可以在jar包中拿到文件流
        InputStream inputStream = resource.getInputStream();
        //InputStream inputStream = new FileInputStream(ResourceUtils.getFile(filePath));
        Workbook wb = null;
        try {
            if (filePath.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(inputStream);
                return export(wb, dataMap, dataList);
            } else if (filePath.endsWith(".xls")) {
                wb = new HSSFWorkbook(inputStream);
                return export(wb, dataMap, dataList);
            } else {
                throw new RuntimeException("模板无效 , " + filePath + " , eg:.xlsx or xls");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static <T, K> Workbook export(Workbook wb, K dataMap, List<T> dataList) throws Exception {
        return export(wb, 0, null, dataMap, dataList, null, null);
    }

    public static <T, K> Workbook export(String filePath, K dataMap, List<T> dataList, StringRedisTemplate stringRedisSTemplate, String exportId) throws Exception {
        InputStream inputStream = new FileInputStream(ResourceUtils.getFile(filePath));
        //InputStream inputStream = new FileInputStream(filePath);
        Workbook wb = null;
        try {
            if (filePath.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(inputStream);
                return export(wb, dataMap, dataList, stringRedisSTemplate, exportId);
            } else if (filePath.endsWith(".xls")) {
                wb = new HSSFWorkbook(inputStream);
                return export(wb, dataMap, dataList, stringRedisSTemplate, exportId);
            } else {
                throw new RuntimeException("模板无效 , " + filePath + " , eg:.xlsx or xls");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static <T, K> Workbook export(Workbook wb, K dataMap, List<T> dataList, StringRedisTemplate stringRedisSTemplate, String exportId) throws Exception {
        return export(wb, 0, null, dataMap, dataList, stringRedisSTemplate, exportId);
    }

    @SuppressWarnings("unused")
    public static <T, K> Workbook export(Workbook wb, int sheetNum, String sheetName, K dataMap, List<T> dataList, StringRedisTemplate stringRedisSTemplate, String exportId) throws Exception {
        ByteArrayOutputStream byteArrayOut = null; //读进图片
        HttpURLConnection conn = null;

        //获取创建工作簿的第一页
        Sheet sheet = wb.getSheetAt(sheetNum);
        if (sheet == null) {
            sheet = wb.createSheet(sheetName != null ? sheetName : "Sheet" + (sheetNum + 1));
        } else if (sheetName != null) {
            wb.setSheetName(sheetNum, sheetName);
        }
        Drawing patriarch = sheet.createDrawingPatriarch();

        int listRowIndex = -1;
        //获取当前工作簿的行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        String[] nameArr = null;//数据列表的名称
        CellStyle[] styleArr = null;
        List<Integer> pictureIndex = null;
        Map<String, Object> mergeMap = null;
        short rowHeight = 0;
        for (int i = 0; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            int column = row.getLastCellNum();
            for (int cIndex = 0; cIndex < column; cIndex++) {
                Cell cell = row.getCell(cIndex);
                if (cell != null && StringUtils.isNotEmpty(cell.getStringCellValue())) {
                    String cellValue = cell.getStringCellValue();
                    //#开头为
                    if (cellValue.startsWith("#.")) {
                        if (nameArr == null) {
                            nameArr = new String[column];
                        }
                        if (styleArr == null) {
                            styleArr = new CellStyle[column];
                        }
                        if (rowHeight == 0) {
                            rowHeight = row.getHeight();
                        }
                        //记录列表要插入的行数
                        if (listRowIndex < 0) {
                            listRowIndex = i;
                        }
                        nameArr[cIndex] = cellValue.replace("#.", "").trim();
                        styleArr[cIndex] = cell.getCellStyle();
                        cell.setCellValue("");
                    } else if (cellValue.startsWith("#M.")) {
                        //需要合并单元格的数据
                        if (nameArr == null) {
                            nameArr = new String[column];
                        }
                        if (styleArr == null) {
                            styleArr = new CellStyle[column];
                        }
                        if (rowHeight == 0) {
                            rowHeight = row.getHeight();
                        }
                        //记录列表要插入的行数
                        if (listRowIndex < 0) {
                            listRowIndex = i;
                        }
                        nameArr[cIndex] = cellValue.replace("#M.", "").trim();
                        styleArr[cIndex] = cell.getCellStyle();
                        if (mergeMap == null) {
                            mergeMap = new HashMap<String, Object>();
                        }
                        mergeMap.put("cIndex_" + cIndex, cIndex);
                        cell.setCellValue("");
                    } else if (cellValue.startsWith("#P.")) {//图片
                        if (nameArr == null) {
                            nameArr = new String[column];
                        }
                        if (styleArr == null) {
                            styleArr = new CellStyle[column];
                        }
                        if (rowHeight == 0) {
                            rowHeight = row.getHeight();
                        }
                        //记录列表要插入的行数
                        if (listRowIndex < 0) {
                            listRowIndex = i;
                        }
                        nameArr[cIndex] = cellValue.replace("#P.", "").trim();
                        styleArr[cIndex] = cell.getCellStyle();
                        cell.setCellValue("");
                        if (pictureIndex == null) {
                            pictureIndex = new ArrayList<Integer>();
                        }
                        pictureIndex.add(cIndex);
                    } else if (cellValue.indexOf("${") > -1 && cellValue.indexOf("}") > -1) {
                        String v = "";
                        do {
                            if (cellValue.indexOf("${") > 0) {
                                v += cellValue.substring(0, cellValue.indexOf("${"));
                            }
                            cellValue = cellValue.substring(cellValue.indexOf("${") + 2);
                            if (dataMap != null) {
                                Object o = getFieldValue(dataMap, cellValue.substring(0, cellValue.indexOf("}")).trim());
                                v += o != null ? o instanceof Date ? defaultDateFormat.format((Date) o) : o.toString() : "";
                            }
                            cellValue = cellValue.substring(cellValue.indexOf("}") + 1);
                        } while (cellValue.indexOf("${") > -1 && cellValue.indexOf("}") > -1);
                        v += cellValue;
                        //String v=dataMap!=null?getValue(dataMap.get(cellValue.substring(cellValue.indexOf("${")+2,cellValue.indexOf("}")).trim())):"";
                        //cellValue=cellValue.substring(0, cellValue.indexOf("${"))+v+cellValue.substring(cellValue.indexOf("}")+1);
                        cell.setCellValue(v);
                    }
                }
            }
        }
        if (listRowIndex > -1 && dataList != null && !dataList.isEmpty() && nameArr != null) {
            ExportStatus status = new ExportStatus();
            status.setStatus(ResponseMsgEnum.EXPORT_DATA);
            status.setTotal(dataList.size());
            for (int index = 0; index < dataList.size(); index++) {
                setExportStatus(stringRedisSTemplate, exportId, status, index);
                Row row = sheet.createRow(listRowIndex);
                row.setHeight(rowHeight);
                T t = dataList.get(index);
                for (int cIndex = 0; cIndex < nameArr.length; cIndex++) {
                    String name = nameArr[cIndex];
                    Cell cell = row.createCell(cIndex);
                    if (StringUtils.isEmpty(name)) {
                        continue;
                    }
                    if ("NO".equals(name)) {//序号
                        cell.setCellValue(index + 1);
                        cell.setCellStyle(styleArr[cIndex]);
                    } else {
                        Object filedValue = getFieldValue(t, name);
                        if (pictureIndex != null && pictureIndex.contains(cIndex)) {
                            //嵌入图片
                            try {
                                byteArrayOut = new ByteArrayOutputStream();
                                String picUrl = filedValue != null ? filedValue.toString() : "";
                                if (StringUtils.isEmpty(picUrl)) {
                                    continue;
                                }
                                picUrl = picUrl.replaceAll(" ", "");
                                if (!picUrl.toLowerCase().startsWith("http") && !picUrl.toLowerCase().startsWith("https")) {
                                    setCellValue(cell, picUrl);
                                    continue;
                                }
                                URL url = new URL(picUrl);
                                //打开链接
                                conn = (HttpURLConnection) url.openConnection();
                                //设置请求方式为"GET"
                                conn.setRequestMethod("GET");
                                //超时响应时间为5秒
                                conn.setConnectTimeout(5 * 1000);
                                //通过输入流获取图片数据
                                InputStream inStream = conn.getInputStream();
                                //得到图片的二进制数据，以二进制封装得到数据，具有通用性
                                byte[] byteData = readInputStream(inStream);

                                /**anchor主要用于设置图片的属性
                                 * 该构造函数有8个参数
                                 * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                                 * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
                                 * excel中的cellNum和rowNum的index都是从0开始的
                                 *
                                 */
                                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cIndex, listRowIndex, (short) cIndex + 1, listRowIndex + 1);

                                //Sets the anchor type （图片在单元格的位置）
                                //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.

                                anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
                                patriarch.createPicture(anchor, wb.addPicture(byteData, XSSFWorkbook.PICTURE_TYPE_JPEG));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        } else {
                            setCellValue(cell, filedValue);
                            cell.setCellStyle(styleArr[cIndex]);
                            //cell.setCellValue(getFieldValue(t, name).toString());
                        }
                        //合并单元格
                        if (mergeMap != null && mergeMap.containsKey("cIndex_" + cIndex)) {
                            if (!mergeMap.containsKey("value_" + cIndex)) {
                                mergeMap.put("value_" + cIndex, filedValue != null ? filedValue.toString() : "");
                                mergeMap.put("rowIndex_" + cIndex, listRowIndex);
                            } else if (!mergeMap.get("value_" + cIndex).equals((filedValue != null ? filedValue.toString() : ""))) {
                                CellRangeAddress region = new CellRangeAddress((Integer) mergeMap.get("rowIndex_" + cIndex), listRowIndex - 1, cIndex, cIndex);
                                sheet.addMergedRegion(region);
                                mergeMap.put("value_" + cIndex, filedValue != null ? filedValue.toString() : "");
                                mergeMap.put("rowIndex_" + cIndex, listRowIndex);
                            } else if (index == dataList.size() - 1 && mergeMap.get("rowIndex_" + cIndex) != null) {
                                CellRangeAddress region = new CellRangeAddress((Integer) mergeMap.get("rowIndex_" + cIndex), listRowIndex, cIndex, cIndex);
                                sheet.addMergedRegion(region);
                            }
                        }
                    }

                }
                listRowIndex++;
            }
        }
        return wb;
    }

    private static void setExportStatus(StringRedisTemplate stringRedisSTemplate, String exportId, ExportStatus status, int index) {
        try {
            if (stringRedisSTemplate != null && StringUtils.isNotEmpty(exportId)) {
                status.setStatus(index, index);
                stringRedisSTemplate.opsForValue().set(Constants.REDIS_PREFIX_EXPORT_STATUS + exportId, JSON.toJSONString(status), Constants.REDIS_IMPORT_STATUS_TIME, TimeUnit.SECONDS);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Sheet复制
     *
     * @param fromSheet
     * @param toSheet
     * @param copyValueFlag
     */
    public static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet,
                                 boolean copyValueFlag) {
        //合并区域处理
        mergerRegion(fromSheet, toSheet);
        for (Iterator<Row> rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            Row tmpRow = rowIt.next();
            Row newRow = toSheet.createRow(tmpRow.getRowNum());
            //行复制
            copyRow(wb, tmpRow, newRow);
        }
    }

    /**
     * 行复制功能
     *
     * @param fromRow
     * @param toRow
     */
    public static void copyRow(Workbook wb, Row fromRow, Row toRow) {
        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = (Cell) cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     */
    public static void mergerRegion(Sheet fromSheet, Sheet toSheet) {
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(mergedRegionAt);
        }
    }

    /**
     * 复制单元格
     *
     * @param fromCell
     * @param toCell   true则连同cell的内容一起复制
     */
    public static void copyCell(Workbook wb, Cell fromCell, Cell toCell) {
		/*CellStyle newstyle=wb.createCellStyle();
		copyCellStyle(fromCell.getCellStyle(), newstyle);
		toCell.setEncoding(fromCell.getEncoding());
		//样式
		toCell.setCellStyle(newstyle);*/
        // 不同数据类型处理
        int fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == Cell.CELL_TYPE_STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == Cell.CELL_TYPE_BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == Cell.CELL_TYPE_ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == Cell.CELL_TYPE_FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        }
    }

    private static byte[] readInputStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }

    public static void setCellValue(Cell cell, Object value) {
        if (cell == null || value == null) {
            return;
        }
        if (value instanceof Integer) {
            cell.setCellValue((int) value);
        } else if (value instanceof Date) {
            cell.setCellValue(defaultDateFormat.format((Date) value));
        } else {
            if (value.toString().contains("\r\n")) {
                cell.setCellValue(new XSSFRichTextString(value.toString()));
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }

    private static Object getFieldValue(Object obj, String name) {
        try {
            Field field = getField(obj.getClass(), name);
            if (field == null) {
                Class superClass = obj.getClass().getSuperclass();
                field = superClass != null ? getField(superClass, name) : null;
            }
            if (field == null) {
                return "";
            }
            field.setAccessible(true);//设置对象的访问权限，保证对private的属性的访问
            return field.get(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }


    private static Field getField(Class c, String name) {
        try {
            return c.getDeclaredField(name);
        } catch (Exception e) {
        }
        return null;
    }


    public static String getValue(Object o) {
        if (o == null) {
            return "";
        } else {
            return o.toString();
        }
    }

}